﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    //聯貸
    public class DA_CIB006:DA_Matrix
    {
        public DA_CIB006()
        {
            tableName = "企金";
        }

        public DataTable GetSummaryData(string p_list,string[] p_yms)
        {
            string[] fldList = p_list.Split(new char[] { ',' });
            string criteriaYM = "A.YYYYMM IN ({0})";
            string fld1 = string.Empty;
            StringBuilder sb = new StringBuilder();
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            foreach (string fld in fldList)
            {
                if (fld.Equals("LNAC_NAME"))
                {
                    fld1 = ",dbo.ReplaceCIBValue(a.LNAC_NAME,1)LNAC_NAME";
                }                    
                sb.AppendFormat(",A.{0}", fld);                
                sb2.AppendFormat(",A.{0} = B.{0}",fld);
            }
            foreach (string mon in p_yms)
            {
                sb1.AppendFormat(",'{0}'", mon);
            }


            //GetCriteriaString();
            string sql = string.Format(getSummaryStatement()
                ,string.Format(criteriaYM,sb1.ToString().Substring(1))
                ,string.Empty
                ,getMultiMonthField(p_yms)
                ,sb2.ToString().Replace(","," AND ")
                ,fld1
                ,sb.ToString()
                );
            return getData(sql, "LQ", DBOpt.MSSQL);
        }

        public DataTable GetDetailData(Dictionary<string,string> p_dict ,string[] p_yms)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sb1 = new StringBuilder();
            string criteriaStr, sql, criteriaYM;
            criteriaStr = "AND {0} = '{1}'";
            criteriaYM = "A.YYYYMM IN ({0})";
            foreach (KeyValuePair<string, string> key in p_dict)
            {
                if (key.Key.ToString().ToLower().Equals("lnac_id"))
                {
                    sb.AppendFormat("AND LNAC_ID = dbo.myEncryptDecryptString('{0}')"
                        , key.Value.ToString());
                }
                else
                {
                    sb.AppendFormat(criteriaStr, key.Key.ToString()
                        , key.Value.ToString());
                }
            }
            foreach (string mon in p_yms)
            {
                sb1.AppendFormat(",'{0}'", mon);
            }
            sql = string.Format(getDetailStatement()
                ,string.Format(criteriaYM,sb1.ToString().Substring(1))
                ,sb.ToString()
                , Utility.GetCIBDetailField("A.")
                );

            return getData(sql, "excel", DBOpt.MSSQL);
        }

        protected string getSummaryStatement()
        {
            /*
             *{0} A.YYYYMM IN ('201103', '201104')
             *{1} GROUP BY 預設是A.YYYYMM,A.CUR + 其它欄位
             *{2} ,MAX(CASE WHEN A.YYYYMM='{0}' 
		THEN CONVERT(NVARCHAR,CAST(ROUND(A.SUM_CAL承作利率/B.TOT_BAL,7) AS MONEY),1)
		END) [承作利率_{0}]		
	,MAX(CASE WHEN A.YYYYMM='{0}' 
		THEN CONVERT(NVARCHAR,CAST(ROUND(A.SUM_CALFTP_RATE/B.TOT_BAL,7) AS MONEY),1)
		END) [FTP_RATE_{0}]				
	,MAX(CASE WHEN A.YYYYMM='201103' 
		THEN CONVERT(NVARCHAR,CAST(ROUND(A.SUM_CAL加減碼/B.TOT_BAL,7) AS MONEY),1)
		END) [加減碼_{0}]			
	,MAX(CASE WHEN A.YYYYMM='201103' 
		THEN CONVERT(NVARCHAR,CAST(B.TOT_餘額 AS MONEY),1)
		END) [餘額_{0}]		
             */
            return @"
SELECT 
	A.CUR
    ,A.Branch
    ,dbo.myEncryptDecryptString(A.lnac_id)lnac_id
    {4}
    {1}
    {2}
FROM (
SELECT 
    A.YYYYMM
    ,LEFT(A.帳號,3) Branch
    ,A.CUR
    ,lnac_id
    {5}    
    , SUM(FTP_RATE * 均額)  AS SUM_CALFTP_RATE
    , SUM(承作利率 * 均額) AS SUM_CAL承作利率
    , SUM(加減碼 * 均額)   AS SUM_CAL加減碼
FROM 企金 A INNER JOIN LM009QS B
	ON A.YYYYMM = B.YYYYMM AND LEFT(A.帳號,14) = B.fld1
WHERE A.均額 > 0
AND {0}
GROUP BY A.YYYYMM,lnac_id,A.CUR{5},LEFT(A.帳號,3)
 )A	INNER JOIN (
SELECT 	
	A.YYYYMM
	,A.CUR
    ,lnac_id
    {5}
	,SUM(餘額) AS TOT_餘額
	,SUM(均額) AS TOT_BAL    
FROM 企金 A INNER JOIN LM009QS B
ON A.YYYYMM = B.YYYYMM AND LEFT(A.帳號,14) = B.fld1
WHERE A.均額 > 0
AND {0}
GROUP BY A.YYYYMM,lnac_id,A.CUR{5}
)B ON A.YYYYMM = B.YYYYMM AND A.CUR = B.CUR {3}
GROUP BY A.lnac_id,A.CUR{5},A.Branch
";
        }

        protected string getDetailStatement()
        {
            /*
             * {0}→企金.YYYYMM
             * {1}→指標利率
             * SELECT *
FROM 企金 A INNER JOIN LM009QS B
	ON A.YYYYMM = B.YYYYMM AND LEFT(A.帳號,14) = B.fld1
WHERE A.均額 > 0
AND A.CUR = 'TWD'
AND A.指標利率 = '004012'
AND a.YYYYMM IN ('201106')
             */
            return @"
SELECT {2}
FROM 企金 A INNER JOIN LM009QS B
	ON A.YYYYMM = B.YYYYMM AND LEFT(A.帳號,14) = B.fld1
WHERE A.均額 > 0
AND {0}
{1}";
        }

        public override DataSet GetCriteria()
        {
            tableName = "LM009QS";
            return base.GetCriteria();
        }

        public DataTable GetGroupBy()
        {
            DA_MISParameter para = new DA_MISParameter();
            DataTable dt = para.GetParameter("CIB", "CIB006");
            return dt;
        }

        public override string[] GetDistinctField()
        {
            return new string[]{"YYYYMM"};
        }

        protected string getMultiMonthField(string[] p_months)
        {
            string fld = @"
	,MAX(CASE WHEN A.YYYYMM='{0}' 
		THEN CONVERT(NVARCHAR,CAST(ROUND(A.SUM_CAL承作利率/B.TOT_BAL,7) AS MONEY),1)
		END) [承作利率_{0}]		
	,MAX(CASE WHEN A.YYYYMM='{0}' 
		THEN CONVERT(NVARCHAR,CAST(ROUND(A.SUM_CALFTP_RATE/B.TOT_BAL,7) AS MONEY),1)
		END) [FTP_RATE_{0}]				
	,MAX(CASE WHEN A.YYYYMM='{0}' 
		THEN CONVERT(NVARCHAR,CAST(ROUND(A.SUM_CAL加減碼/B.TOT_BAL,7) AS MONEY),1)
		END) [加減碼_{0}]			
	,MAX(CASE WHEN A.YYYYMM='{0}' 
		THEN CONVERT(NVARCHAR,CAST(B.TOT_餘額 AS MONEY),1)
		END) [餘額_{0}]		
	,MAX(CASE WHEN A.YYYYMM='{0}' 
		THEN CONVERT(NVARCHAR,CAST(B.TOT_BAL AS MONEY),1)
		END) [均額_{0}]		
";
            StringBuilder sb = new StringBuilder();
            foreach (string mon in p_months)
            {
                sb.AppendFormat(fld, mon);
            }
            return sb.ToString();
        }
    }
}
